# # coding=utf-8
import openpyxl
import xlrd
import time
import requests
import json
import datetime
import random
import string
from auto_interface.testting import rpb_all_param
from auto_interface.testting import oracle_conn


class Excel_rpb():
    @classmethod
    def excel(self, file_path=rpb_all_param.Param.path):
        data = xlrd.open_workbook(file_path)
        table = data.sheet_by_index(0)

        # 定义一个新的excel
        new_data = openpyxl.Workbook()
        new_sheet = new_data.active

        # 查看工作表
        data.sheet_names()
        # print("全部sheet页如下：\n" + str(data.sheet_names()))
        # print("总行数：" + str(table.nrows))
        start_time = time.strftime('%Y-%m-%d %H:%M:%S')
        starttime = datetime.datetime.now()
        print("开始时间：" + str(start_time))

        for i in range(0, table.nrows):
            """
            print("卡口类型：%s" % rpbtype)
            print("通道编号：%s" % rpbid)
            print("车牌号：%s" % ve_licenseno)
            print("出入库类型：%s" % inexp_type)
            print("进出境关别：%s" % imep_code)
            print("进出方向：%s" % ioareaflag)
            print("运输方式：%s" % trspModecd)
            print("报关单布控解控：%s" % EntryNo_ISCTRL)
            print("毛重：%s" % wt_gross)
            print("集装箱：%s" % container_no)
            print("开关：%s" % kaiguan)
            print("重量：%s" % other_wt)
            print("集装箱时间：%s" % other_container_time)
            print("车牌号时间：%s" % other_ve_time)
            print("备注：%s" % rmk)
            print("预期结果(提示语)：%s" % expect)
            print("是否通过：%s" % ispass)
            print("实际结果：%s" % actual)
            """
            # 表字段信息
            for j in range(0, table.ncols):
                value = Excel_rpb().excel_get(table=table, i=i, j=j)
                new_sheet.cell(i + 1, j + 1, str(value)).number_format = '@'
            rpbid = Excel_rpb().excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["rpbid"]-1)
            imep_code = Excel_rpb().excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["imep_code"]-1)
            inexp_type = Excel_rpb().excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["inexp_type"]-1)
            ioareaflag = Excel_rpb().excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["ioareaflag"]-1)
            trspModecd = Excel_rpb().excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["trspModecd"]-1)
            EntryNo_ISCTRL = Excel_rpb().excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["EntryNo_ISCTRL"]-1)

            if i != 0:
                print("======生成第%s条用例数据======" % i)
                info = Excel_rpb().transformation(rpbid=rpbid, inexp_type=inexp_type, imep_code=imep_code,ie_type=ioareaflag, trspModecd=trspModecd,EntryNo_ISCTRL=EntryNo_ISCTRL)
                print(info)
                new_sheet.cell(i + 1, rpb_all_param.Param.excel_rows_param["ve_licenseno"], str(info)).number_format = '@'
                sql = 'update '
                # oracle_conn.Oracle_conn().update()

        endtime = datetime.datetime.now()
        use_time = (endtime-starttime).seconds
        print("共%s条数据，耗时：%s秒。" % (table.nrows - 1, use_time))
        new_data.save(rpb_all_param.Param.new_path)

    def transformation(self,rpbid,inexp_type,imep_code,ie_type,trspModecd,EntryNo_ISCTRL):
        orgArea = 'L'
        org_id = '103300'
        rltEntryNo = "224920220506000001"

        if '480017' in rpbid or '480018' in rpbid:
            orgArea = 'D'
            org_id = '103240'
        elif '480015' in rpbid or '480016' in rpbid:
            orgArea = 'J'
            org_id = '103640'

        if EntryNo_ISCTRL == 1:
            rltEntryNo ="224920220506000002"

        vehicleno = Excel_rpb().sava(j=1,org_id=org_id,inexp_type=inexp_type,imep_code=imep_code,ie_type=ie_type,trspModecd=trspModecd,orgArea=orgArea,rltEntryNo=rltEntryNo,vehi=random.choices(rpb_all_param.Param.vehi)[0],cleNo=chr(random.randint(ord('A'),ord('Z'))))
        return vehicleno

    def sava(self,j,org_id,imep_code,inexp_type,ie_type,trspModecd,orgArea,rltEntryNo,vehi,cleNo):

        headers = rpb_all_param.Param.headers
        headers.update({"orgId": str(org_id),"orgArea":orgArea})

        v_data = "False"
        vehicleNo = 0
        while v_data:
            # vehicleNo = vehi + cleNo + '%05d' % random.sample(string.digits, 5)
            vehicleNo = vehi + cleNo + '%05d' % random.randrange(0,99999)

            pack_lie = Excel_rpb().post_requests(headers=headers,
                                              url='http://192.168.125.226:9024/getPackageByVehicleNo',
                                              data=json.dumps({"vehicleNo":(vehicleNo)}))
            v_data = pack_lie.get("map").get("data")
            # v += 1
        print(vehicleNo)

        if inexp_type in ("1", "5", '2'):
            Excel_rpb().sava_1_5(j=j,org_id=org_id,imep_code=imep_code,inexp_type=inexp_type,ie_type=ie_type,trspModecd=trspModecd,headers=headers,vehicleNo=vehicleNo,rltEntryNo=rltEntryNo)
        elif inexp_type == "4":
            Excel_rpb().sava_4(j=j,org_id=org_id,imep_code=imep_code,inexp_type=inexp_type,trspModecd=trspModecd,headers=headers,vehicleNo=vehicleNo)
        elif inexp_type == "6" and ie_type == 'I':
            Excel_rpb().sava_6(j=j,org_id=org_id,imep_code=imep_code,trspModecd=trspModecd,headers=headers,vehicleNo=vehicleNo)
        return vehicleNo

    def sava_1_5(self,j,org_id,imep_code,inexp_type,ie_type,trspModecd,headers,vehicleNo,rltEntryNo):
        i = 0
        pack_list = []
        while i < j:
            ware_param = rpb_all_param.Param.ware_param
            ware_param.update({"orgId": org_id, "impexpPortcd": imep_code, "inExpType": inexp_type, "ieTypecd": ie_type, "rltEntryNo": rltEntryNo,"trspModecd":str(trspModecd)})

            response = Excel_rpb().post_requests(headers=headers,url=rpb_all_param.Param.waresave_url, data=json.dumps(ware_param))
            ware_id = response.get("map").get("data")

            # 查询
            ware_det = Excel_rpb().post_requests(headers=headers,url='http://192.168.125.162:9041/getBscDetails',data=json.dumps({"id":ware_id}))
            whRecPreentNo = ware_det.get("map").get("data").get("whRecPreentNo")
            # 提发货确认
            print("提发货确认")
            a = Excel_rpb().post_requests(headers=headers,url='http://192.168.125.162:9041/submit',data=json.dumps({"bscIds":[ware_id]}))
            time.sleep(5)

            # 海关查询
            print("海关查询")
            is_con = False
            xtjg_id = ''
            n = 0
            while is_con is False:
                time.sleep(2)
                print(whRecPreentNo)
                xtjg_data = Excel_rpb().post_requests(headers=headers,url='http://192.168.125.166:9041/getList',data=json.dumps({"like_whRecPreentNo": whRecPreentNo, "optStatus": "17", "size": 10,"page": 1}))
                # print(xtjg_data)
                if len(xtjg_data.get("map").get("data").get("content")) != 0:
                    is_con = True
                    xtjg_id = xtjg_data.get("map").get("data").get("content")[0].get("id")
                if n==5:
                    print("提发货：%s异常" % whRecPreentNo)
                    break
                n += 1

            # 海关审核
            print("海关审核")
            Excel_rpb().post_requests(headers=headers,
                                          url='http://192.168.125.166:9041/audit',
                                                  data=json.dumps(
                                                      {"auditType":"Y","bscIds":[str(xtjg_id)],"level":"1"}))
            port_pa = Excel_rpb().save_port(org_id=org_id,
                                            imep_code=imep_code,
                                            inexp_type=inexp_type,
                                            ie_type=ie_type,
                                            headers=headers,
                                            vehicleNo=vehicleNo,
                                            ware_id=ware_id,
                                            whRecPreentNo=whRecPreentNo)
            port_no = port_pa[0]
            port_id = port_pa[1]

            pack_dt = {"sasPassportPreentNo": port_no, "sasOptStatus": "5", "passportTypecd": str(inexp_type),
                            "customCode": "2249", "ioTypecd": ie_type, "rmk": port_id}
            print("第%d条卡口凭证：%s"%(i+1, port_no))
            pack_list.append(pack_dt)

            i += 1

        # 生成装车单
        pack_dec = Excel_rpb().save_pack(org_id=org_id,
                            imep_code=imep_code,
                            inexp_type=inexp_type,
                            ie_type=ie_type,
                            headers=headers,
                            vehicleNo=vehicleNo,
                            pack_list=pack_list )
        print(pack_dec)


    def sava_4(self,j,org_id,imep_code,inexp_type,trspModecd,headers,vehicleNo):
        i = 0
        pack_list = []
        while i < j:
            # print(i)
            ware_param = rpb_all_param.Param.ware_4_param_i
            ware_param.update({"orgId": org_id, "impexpPortcd": imep_code, "inExpType": inexp_type, "ieTypecd": "I","trspModecd":str(trspModecd)})

            response = Excel_rpb().post_requests(headers=headers,
                                                 url=rpb_all_param.Param.waresave_url, data=json.dumps(ware_param))
            ware_id = response.get("map").get("data")

            # 查询
            # print("查询")
            ware_det = Excel_rpb().post_requests(headers=headers,
                                                 url='http://192.168.125.162:9041/getBscDetails',
                                                 data=json.dumps({"id":ware_id}))
            whRecPreentNo = ware_det.get("map").get("data").get("whRecPreentNo")
            # 提发货确认
            print("提发货确认")
            a = Excel_rpb().post_requests(headers=headers,
                                          url='http://192.168.125.162:9041/submit',
                                      data=json.dumps({"bscIds":[ware_id]}))
            time.sleep(5)

            # 海关查询
            print("海关查询")
            is_con = False
            while is_con is False:
                time.sleep(2)
                xtjg_data = Excel_rpb().post_requests(headers=headers,
                                                      url='http://192.168.125.166:9041/getList',
                                                  data=json.dumps(
                                                      {"like_whRecPreentNo": whRecPreentNo, "optStatus": "3", "size": 10,
                                                       "page": 1}))
                if len(xtjg_data.get("map").get("data").get("content")) != 0:
                    is_con = True

            # 出区单证
            ware_param_e = rpb_all_param.Param.ware_4_param_i
            ware_param_e.update({"orgId": org_id, "impexpPortcd": imep_code, "inExpType": inexp_type, "ieTypecd": "E",
                               "rltWhRecNo": whRecPreentNo, "trspModecd": str(trspModecd)})

            response_E = Excel_rpb().post_requests(headers=headers,
                                                 url=rpb_all_param.Param.waresave_url, data=json.dumps(ware_param_e))
            ware_E_id = response_E.get("map").get("data")

            # 查询
            # print("查询")
            ware_det = Excel_rpb().post_requests(headers=headers,
                                                 url='http://192.168.125.162:9041/getBscDetails',
                                                 data=json.dumps({"id": ware_E_id}))
            whRecPreentNo_e = ware_det.get("map").get("data").get("whRecPreentNo")
            # 提发货确认
            print("提发货确认")
            a = Excel_rpb().post_requests(headers=headers,
                                          url='http://192.168.125.162:9041/submit',
                                          data=json.dumps({"bscIds": [ware_E_id]}))
            time.sleep(5)

            # 生成卡口凭证
            port_pa = Excel_rpb().save_port(org_id=org_id,
                                            imep_code=imep_code,
                                            inexp_type=inexp_type,
                                            ie_type='I',
                                            headers=headers,
                                            vehicleNo=vehicleNo,
                                            ware_id=ware_id,
                                            whRecPreentNo=whRecPreentNo)
            port_no = port_pa[0]
            port_id = port_pa[1]

            pack_dt = {"sasPassportPreentNo": port_no, "sasOptStatus": "5", "passportTypecd": str(inexp_type),
                            "customCode": "2249", "ioTypecd": 'I', "rmk": port_id}
            print("第%d条卡口凭证：%s"%(i+1, port_no))
            pack_list.append(pack_dt)

            i += 1

        # 生成装车单
        Excel_rpb().save_pack(org_id=org_id,
                                         imep_code=imep_code,
                                         inexp_type=inexp_type,
                                         ie_type='I',
                                         headers=headers,
                                         vehicleNo=vehicleNo,
                                         pack_list=pack_list)

    def sava_6(self,j,org_id,imep_code,trspModecd,headers,vehicleNo):
        i = 0
        pack_list = []
        while i < j:
            # print(i)
            bill_param = rpb_all_param.Param.ware_param
            bill_param.update({"orgId": org_id, "impexpPortcd": imep_code, "grossWt": "5000", "billLodNo": "MMK1001", "voyageNo":"0034W", "shipNameEn":"shipNameEn"+'%05d' % i,"trspModecd":str(trspModecd)})

            response = Excel_rpb().post_requests(headers=headers,
                                                 url='http://192.168.125.162:9038/save', data=json.dumps(bill_param))
            print(response)
            bill_id = response.get("map").get("id")

            # 查询
            # print("查询")
            ware_det = Excel_rpb().post_requests(headers=headers,
                                                 url='http://192.168.125.162:9038/getDetail',
                                                 data=json.dumps({"id":str(bill_id)}))
            orderNo = ware_det.get("map").get("data").get("orderNo")
            # 提发货确认
            print("径予确认")
            a = Excel_rpb().post_requests(headers=headers,
                                          url='http://192.168i.125.162:9038/declare',
                                      data=json.dumps({"ids":str(bill_id)}))
            time.sleep(5)

            # 海关查询
            print("海关查询")
            is_con = False
            xtjg_id = ''
            while is_con is False:
                time.sleep(5)
                xtjg_data = Excel_rpb().post_requests(headers=headers,
                                                      url='http://192.168.125.166:9038/getBillDirBscListCustom',
                                                  data=json.dumps(
                                                      {"orderNo": orderNo, "optStatus": "5", "size": 10,
                                                       "page": 1}))
                # print(xtjg_data)
                if len(xtjg_data.get("map").get("data").get("content")) != 0:
                    is_con = True
                    xtjg_id = xtjg_data.get("map").get("data").get("content")[0].get("id")

            # print("查询")
            ware_det = Excel_rpb().post_requests(headers=headers,
                                                 url='http://192.168.125.162:9038/getDetail',
                                                 data=json.dumps({"id": str(bill_id)}))
            billNo = ware_det.get("map").get("data").get("billNo")

            # 生成卡口凭证
            port_pa = Excel_rpb().save_port(org_id=org_id,
                                 imep_code=imep_code,
                                 inexp_type='6',
                                 ie_type='E',
                                 headers=headers,
                                 vehicleNo=vehicleNo,
                                  ware_id=bill_id,
                                  whRecPreentNo=billNo)
            port_no = port_pa[0]
            port_id = port_pa[1]
            pack_dt = {"sasPassportPreentNo": port_no, "sasOptStatus": "5", "passportTypecd": '6',
                            "customCode": "2249", "ioTypecd": 'I', "rmk": port_id}
            print("第%d条卡口凭证：%s"%(i+1, port_no))
            pack_list.append(pack_dt)

            i += 1

        # 生成装车单
        Excel_rpb().save_pack(org_id=org_id,
                                         imep_code=imep_code,
                                         inexp_type='6',
                                         ie_type='I',
                                         headers=headers,
                                         vehicleNo=vehicleNo,
                                         pack_list=pack_list)


    def save_port(self,org_id,imep_code,inexp_type,ie_type,headers,vehicleNo,ware_id,whRecPreentNo):
        print("生成卡口凭证")
        port_param = rpb_all_param.Param.port_param
        port_param.update({"orgId": org_id, "impexpPortcd": imep_code, "inExpType": inexp_type, "ieTypecd": ie_type})
        port_param.update({"vehicleNo": vehicleNo})

        war_sta = '17'
        while war_sta != '5':
            time.sleep(2)
            ware_det = Excel_rpb().post_requests(headers=headers,
                                                 url='http://192.168.125.162:9041/getBscDetails',
                                                 data=json.dumps({"id": ware_id}))
            war_sta = ware_det.get("map").get("data").get("optStatus")
        # print(war_sta)
        port_save = Excel_rpb().post_requests(headers=headers,
                                              url=rpb_all_param.Param.portsave_url,
                                              data=json.dumps(port_param).replace('103300WH221212018783',
                                                                                  whRecPreentNo))
        port_id = port_save.get("map").get("data")
        port_no = port_save.get("map").get("rltList")[0].get("portNo")

        # 卡口凭证确认
        print("卡口凭证确认")
        response = Excel_rpb().post_requests(headers=headers,
                                             url="http://192.168.125.162:9042/declare",
                                             data=json.dumps({"bscIds": [port_id]}))
        # print(response)
        por_sta = '20'
        while por_sta != '5':
            time.sleep(2)
            por_det = Excel_rpb().post_requests(headers=headers,
                                                url='http://192.168.125.162:9042/getDetail',
                                                data=json.dumps({"id": port_id}))
            por_sta = por_det.get("map").get("data").get("optStatus")
        return port_no, port_id

    def save_pack(self,org_id,imep_code,inexp_type,ie_type,headers,vehicleNo,pack_list):
        print("生成装车单")
        pack_param = rpb_all_param.Param.pack_param
        pack_param.update({"orgId": org_id,
                           "impexpPortcd": imep_code,
                           "inExpType": inexp_type,
                           "iePack": ie_type,
                           "vehicleNo": vehicleNo,
                           "packageSaspassport": pack_list})
        print(pack_param)

        pack_save = Excel_rpb().post_requests(headers=headers,
                                              url=rpb_all_param.Param.packsave_url,
                                              data=json.dumps(pack_param))
        print(pack_save)
        port_id = pack_save.get("map").get("id")
        # print(port_id)

        # 装车单确认
        print("装车单确认")
        pack_dec = Excel_rpb().post_requests(headers=headers,
                                             url="http://192.168.125.164:9055/packageListDeclare",
                                             data=json.dumps({"bscIds": [port_id]}))
        time.sleep(7)
        print(pack_dec)
        return pack_dec


    def post_requests(self, headers, url, data):
        try:
            response = requests.post(url=url, headers=headers, data=data)
            ass_value = response.json()
            return ass_value
        except Exception as E:
            return False, E, "请求异常"

    def excel_get(self, table, i, j):
        value = table.cell(i, j).value
        return value

    def excel_put(self, new_sheet, i, j, param):
        new_sheet.cell(i + 1, j+1, str(param)).number_format = '@'

if __name__ == "__main__":
    # i = 0
    # while i<10:
        # Excel_rpb().sava(j=1,
        #                  org_id='104705',
        #                  inexp_type='1',
        #                  imep_code='2248',
        #                  ie_type='I',
        #                  trspModecd=2,
        #                  orgArea="L",
        #                  rltEntryNo="224920220506000001",
        #                  vehi='进',
        #                  cleNo='A')
        # i += 1
    Excel_rpb().excel()

# 103300
# 103640